package DBConnection;

import org.apache.log4j.Logger;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Locale;

public class DBTools {

    private static final Logger log = Logger.getLogger(DBTools.class);

    //Достаёт объект для работы с базой из пула на сервере
    public static DataSource source;

    static {
        try {
            source = InitialContext.doLookup("java:/jdbc/NCBank");
        } catch (NamingException e) {
            e.printStackTrace();
            System.out.print("\nCan`t open connection to database\n");
        }
    }

    public static BigInteger getNewID() {
        BigInteger newID = null;
        try (Connection connection = source.getConnection();
             CallableStatement statement = connection.prepareCall("{?=call pkg_tools.get_id}"))
        {
            statement.registerOutParameter(1, Types.NUMERIC);
            statement.executeQuery();
            newID = statement.getBigDecimal(1).toBigInteger();
        } catch (Exception ex) {
            ex.printStackTrace();
            log.error("Cannot generate new ID: ", ex);
        }
        return newID;
    }

    public static Integer getNewAccNumber() {
        BigInteger newID = null;
        try (Connection connection = source.getConnection();
             CallableStatement statement = connection.prepareCall("{?=call pkg_tools.get_id}"))
        {
            statement.registerOutParameter(1, Types.NUMERIC);
            statement.executeQuery();
            newID = statement.getBigDecimal(1).toBigInteger();
        } catch (Exception ex) {
            ex.printStackTrace();
            log.error("Cannot generate new ID: ", ex);
        }
        return new Integer(newID.toString().substring(10, 19));
    }

    public static <T> T executeSelect(String query, ResultSetHandler<T> handler){
        ResultSet rs;

        if (query.contains(";")){
            log.debug("Invalid query. Unexpected ';'");
            return null;
        }

        try (Connection connection = source.getConnection();
            java.sql.Statement st = connection.createStatement() ){
            rs = st.executeQuery(query);
            return handler.handle(rs);
        } catch (SQLException e) {
            e.printStackTrace();
            log.debug("Can`t execute query");
        }
        return null;
    }

    public static int executeOtherDMLQuery(String query){
        if (query.contains(";")){
            log.debug("Invalid query. Unexpected ';'.");
            return 0;
        }

        try (Connection connection = source.getConnection();
             java.sql.Statement st = connection.createStatement() ) {
            return st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
            log.debug("Can`t execute query");
        }
        return 0;
    }

    // Выполняет параметризированный запрос, подставляя параметры из params
    public static <T> T executeSelect(String query, ArrayList<Object> params, ResultSetHandler<T> handler){
        ResultSet rs;

        int i = 1;
        try  (Connection connection = source.getConnection();
            java.sql.PreparedStatement st = connection.prepareStatement(query)) {
            for (Object param: params){
                switch (param.getClass().getName()){
                    case "java.lang.String": {
                        st.setString(i, (String)param);
                        i++;
                        break;
                    }
                    case "java.lang.Integer" :
                    case "int": {
                        st.setInt(i, Integer.getInteger(param.toString()));
                        i++;
                        break;
                    }
                    case  "Date": {
                        st.setDate(i, (Date) param);
                        i++;
                        break;
                    }
                    case "java.lang.Double": {
                        st.setDouble(i, (Double)param);
                        i++;
                        break;
                    }
                    case "Float": {
                        st.setFloat(i, (Float)param);
                        i++;
                        break;
                    }
                    case "Long": {
                        st.setLong(i, (Long)param);
                        i++;
                        break;
                    }
                    case "java.math.BigInteger": {
                        st.setBigDecimal(i, new BigDecimal((BigInteger) param));
                        i++;
                        break;
                    }
                    default: log.info("\nWrong parameter");

                }
            }
            rs = st.executeQuery();
            return handler.handle(rs);
        } catch (SQLException e) {
            e.printStackTrace();
            log.debug("Can`t execute query");
        }
        return null;
    }

    public static int executeOtherDMLQuery(String query, ArrayList<Object> params){
        if (query.contains(";")){
            log.debug("Invalid query. Unexpected ';'.");
            return 0;
        }
        int i = 1;
        try  (Connection connection = source.getConnection();
              java.sql.PreparedStatement st = connection.prepareStatement(query)) {
            for (Object param: params){
                if (param != null) {
                    switch (param.getClass().getName()) {
                        case "java.lang.String": {
                            st.setString(i, (String) param);
                            i++;
                            break;
                        }
                        case "java.lang.Integer" :
                        case "int": {
                            st.setInt(i, (int)param); //Integer.getInteger(param.toString())
                            i++;
                            break;
                        }
                        case "java.util.Date": {
                            DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss" , Locale.ENGLISH);
                            st.setString(i, format.format((java.util.Date) param));
                            i++;
                            break;
                        }
                        case "java.lang.Double": {
                            st.setDouble(i, (Double) param);
                            i++;
                            break;
                        }
                        case "Float": {
                            st.setFloat(i, (Float) param);
                            i++;
                            break;
                        }
                        case "Long": {
                            st.setLong(i, (Long) param);
                            i++;
                            break;
                        }
                        case "java.math.BigInteger": {
                            st.setBigDecimal(i, new BigDecimal((BigInteger) param));
                            i++;
                            break;
                        }
                        default:
                            log.info("wrong parameter:  " + param.getClass().getName());

                    }
                }
                else {
                    st.setString(i, null);
                    i++;
                }
            }
            log.info(query);
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.debug("Can`t execute query");
        }
        return 0;
    }

}